#!/usr/bin/env python
######################################################################
#    dia2sql_xml.py - A component of Watchtower.  
#    		Parses an xml output file produced by Dia.
#
#    Copyright (C) 2003  Eric I. Arnoth	<earnoth@comcast.net>   
#
#    This program is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation; either version 2 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program; if not, write to the Free Software
#    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
#
"""
	dia2sql_xml.py 
		Author:	Eric I. Arnoth
		Date:		February 8, 2003

	Parses an xml output file produced by dia.  Places the data into internal
	structures to be accessed through functions.
	Requires PyXML-0.7.1 (http://sourceforge.net/projects/pyxml)

"""
from xml.sax import make_parser, SAXException
import sys
from xml.sax.handler import ContentHandler
import re
import fileinput
import os
import binascii
import types
import os.path
import string
import traceback

# SQL comments
table_header = "/*============================================================================*/"
table_header = string.join((table_header, "/* Table:                                                                    */",table_header), "\n")

sequence_header = "/*============================================================================*/"
sequence_header = string.join((sequence_header, "/* Sequence:                                                                 */",sequence_header), "\n")

foriegn_header = "/*============================================================================*/"
foriegn_header = string.join((foriegn_header, "/* New Foriegn Key Constraint:                                               */",foriegn_header), "\n")


def fill_header(header, text):
	text_len = len(text)
	empty_space = ''
	for char in range(text_len):
		empty_space += " "
	this_header = string.replace(header, empty_space, " "+text, 1)
	return this_header			

# Element names
dia_diagram = 'dia:diagram'
dia_attribute = 'dia:attribute'
dia_composite = 'dia:composite'
dia_object = 'dia:object'
dia_string = 'dia:string'
dia_connections = 'dia:connections'
dia_connection = 'dia:connection'
# Attribute Names
xmlns_dia = 'xmls:dia'
name = 'name'
val = 'val'
value = 'value'
type_ = 'type'
version = 'version'
id = 'id'
handle = 'handle'
to = 'to'
connection = 'connection'
sterotype = 'stereotype'
constraint = 'constraint'
#internal variables different from xml elements and attributes
UMLclass = 'UML - Class'
UMLdep = 'UML - Dependency'
UMLconstraint = 'UML - Constraint'
umlattr = 'umlattribute'
stereotype = 'stereotype'


class column:
	
	def __init__(self, hook1):
		self.hook1 = hook1
		self.hook2 = hook1 + 1
		self.datatype = None
		self.constraint = None
		self.value = None


class reference:
	
	def __init__(self, name):
		self.name = name
		self.local_column = ""
		self.table = ""
		self.foreign_column = ""

		
class dbtable:
	
	def __init__(self):
		self.counter = 8
		self.name = ''
		self.__columns = {}
		self.__references = {}

	def local_ref(self, id, name, colname):
		self.__references[id] = reference(name)
		self.__references[id].local_column = colname

	def remote_ref(self, id, table, colname):
		self.__references[id].table = table
		self.__references[id].foreign_column = colname	

	def get_refs(self):
		return self.__references.keys()	

	def get_ref_name(self, id):
		return self.__references[id].name

	def get_ref_relations(self, id):
		return self.__references[id].local_column, \
					self.__references[id].table, self.__references[id].foreign_column

	def get_ref_table(self, id):				
		return self.__references[id].table

	def get_ref_local(self, id):	
		return self.__references[id].local_column

	def get_ref_foriegn(self, id):
		return self.__references[id].foreign_column	

	def add_column(self, columnname):
		self.__columns[columnname] = column(self.counter)
		self.counter += 2

	def set_col_type(self, columnname, type_):	
		if self.__columns.has_key(columnname):
			self.__columns[columnname].datatype = type_
			return 1
		else:	
			return 0


	def set_col_value(self, colname, thisvalue):
		if self.__columns.has_key(colname):
			self.__columns[colname].value = thisvalue
			return 1
		else:
			return 0

	def set_column_constraint(self, columnname, constraint):
		if self.__columns.has_key(columnname):
			self.columns[columnname].constraint = constraint
			return 1
		else:	
			return 0

	def get_columns(self):
		return self.__columns.keys()	

	def get_col_value(self, colname):
		return self.__columns[colname].value

	def get_col_type(self, colname):
		return self.__columns[colname].datatype

	def get_hooks(self, colname):
		return self.__columns[colname].hook1, self.__columns[colname].hook2

	def hook_col(self, hook):
		col_list = self.__columns.keys()
		for column in col_list:
			if self.__columns[column].hook1==int(hook):
				return column
			elif self.__columns[column].hook2==int(hook):
				return column
				

class DiaContentHandler(ContentHandler):
	"""
		The structure of this class is based around the architecture of PyXML. 
	"""
	def __init__(self, filename):
		"""
			Instanciates the class, initializes the variables.

			Arguments:
				filename - string, the name of the file to be parsed.
			
			Returns:	
				class instance

			Returns:
				Nothing

		"""
		self.__tables = {}
		self.__dependencies = {}
		self.intable = None
		self.buffer = ''
		self.column = 0
		self.column_name = 0
		self.column_type = 0
		self.invalue = 0
		self.intable = 0
		self.table_name = 0
		self.tableid = 0
		self.dep_name = 0
		self.indep =0
		self.system = None
		parser = make_parser()
		parser.setContentHandler(self)
		try:
			parser.parse(open(filename))
		except:
			print '=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-='
			print 'Failure in XML parsing!'
			print 'Did you remember to save the Dia file without compression...?'
			print '=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-='
			traceback.print_exc()
			


	def startElement(self, xmlname, attrs):
		"""
			Called upon encountering the opening of an xml tag.  
		"""
		#self.print_data()
		if xmlname == dia_object:
			if attrs.get(type_) == UMLclass:
				table_id = attrs.get(id)
				self.__tables[table_id] = dbtable()
				self.intable = table_id
			elif attrs.get(type_) == UMLconstraint:
				self.indep = attrs.get(id)
		elif xmlname == dia_string:
			self.buffer = ''
		elif xmlname == dia_composite:
			if attrs.get(type_) == umlattr:
				self.column = 1
		elif xmlname == dia_attribute:
			if attrs.get(name) == name:
				if self.column:
					self.column_name = 1
				if not self.column:
					self.table_name = 1	
			elif attrs.get(name) == type_:
				self.column_type = 1	
			elif attrs.get(name) == value and self.column:
				self.invalue = 1
		elif xmlname == dia_connection:
			if self.indep:
				if not self.tableid:
					self.tableid = attrs.get(to)
					colid = attrs.get(connection)
					colname = self.__tables[self.tableid].hook_col(colid)
					self.__tables[self.tableid].local_ref(self.indep, self.dep_name,\
							  colname)
				elif self.tableid:
					tableid = attrs.get(to)
					colid = attrs.get(connection)
					colname = self.__tables[tableid].hook_col(colid)
					self.__tables[self.tableid].remote_ref(self.indep, tableid, \
							  colname)
					self.indep = 0
					self.tableid = 0


	def endElement(self, xmlname):
		"""
			Called upon entry of a closing tag.
		"""
		if xmlname==dia_string:
			if self.intable:
				if self.table_name:
					table_name = string.replace(self.buffer.strip(), "#", "")
					self.__tables[self.intable].name = table_name
					self.table_name = 0
				elif self.column and self.column_name and not self.column_type and \
							not self.invalue:
					columnname = string.replace(self.buffer.strip(), "#", "")
					self.__tables[self.intable].add_column(columnname)
					self.column_name = columnname
				elif self.column and self.column_name and self.column_type and \
							not self.invalue:
					columntype = string.replace(self.buffer.strip(), "#", "")
					self.__tables[self.intable].set_col_type(self.column_name,\
							  columntype)
					self.column_type = 0
				elif self.column and self.column_name and not self.column_type and \
							self.invalue:
					thisvalue = 	string.replace(self.buffer.strip(), "#", "")
					self.__tables[self.intable].set_col_value(self.column_name, \
							  thisvalue)
					self.invalue = 0
			if self.indep:
				self.dep_name = string.replace(self.buffer.strip(), "#", "")
		if xmlname == dia_object:
			if self.intable:
				self.intable = 0		
		if xmlname == dia_composite:
			self.column = 0	
			self.column_name = 0
				
				
			
	def characters(self, chars):
		self.buffer += chars

	def get_data(self):
		return self.__tables

	def get_last_index(self):
		return self.index

class SQLBuilder:

	def __init__(self, tables):
		self.tables = tables
		self.sql_create = ''
		self.sql_delete = ''
		self.sql_create = ''
		self.sql_create_seq = ''
		self.sql_constraint = ''
		self.additional_constraints = ''

	def create_table(self, table_id):
		table_name = self.tables[table_id].name
		this_table_header = fill_header(table_header, table_name)
		self.sql_create = string.join((self.sql_create, this_table_header), \
				  "\n\n\n")
		self.sql_create = string.join((self.sql_create, \
						"CREATE TABLE %s (" % string.upper(table_name)), "\n")
		return table_name

	def create_columns(self, table_id):

		col_list = self.tables[table_id].get_columns()
		table_name = self.tables[table_id].name
		num_cols = len(col_list)
		for index in range(num_cols):
			colname = col_list[index]

			coltype = self.tables[table_id].get_col_type(colname)
			coltype = self.fix_datatype(coltype)

			colvalue = self.tables[table_id].get_col_value(colname)
			col_statement = self.fix_value(table_name, colname, coltype, colvalue)
			if index < num_cols-1:
				self.sql_create = string.join((self.sql_create, "\n\t", \
							string.upper(col_statement)+","), "")
			else:	
				self.sql_create = string.join((self.sql_create, "\n\t", \
							string.upper(col_statement)), "")


	def create_references(self, table_id):			
		return

	def make_database(self):
		table_list = self.tables.keys()
		for table_id in table_list:
			self.additional_constraints = ''
			table_name = self.create_table(table_id)

			self.create_columns(table_id)
			
			if self.additional_constraints:		
				self.sql_create = \
					string.join((self.sql_create, self.additional_constraints),",\n")

			self.create_references(table_id)
					
			self.sql_create = string.join((self.sql_create, ");"), "\n")	
		
		self.sql_create = string.join((self.sql_create_seq, self.sql_create, \
						self.sql_constraint), "")

	def print_raw_data(self):	
		print '========================='
		table_list = self.tables.keys()
		for table_id in table_list:
			print table_id
			print self.tables[table_id].name
			columns = self.tables[table_id].get_columns()
			for colname in columns:
				print colname, self.tables[table_id].get_col_type(colname), \
						self.tables[table_id].get_hooks(colname), \
						self.tables[table_id].get_col_value(colname)
			references = self.tables[table_id].get_refs()
			for ref in references:
				print self.tables[table_id].get_ref_name(ref), \
						self.tables[table_id].get_ref_relations(ref)
			print '-------------------------'
		print '========================='


class	PostgresBuilder(SQLBuilder):

	def __init__(self, tables):
		SQLBuilder.__init__(self, tables)

	def fix_datatype(self, coltype):
		if coltype == 'datetime':
			return 'timestamp'
		else:
			return coltype
		
	def fix_value(self, table_name, colname, coltype, colvalue):		
		if colvalue == 'identity':
			seq_statement = "CREATE SEQUENCE %s\n" + \
					"INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;"
			seq_name = string.join((table_name, colname, "req"), "_")
			seq_statement = string.upper(seq_statement % seq_name)
			self.sql_create_seq = string.join((self.sql_create_seq, \
							fill_header(sequence_header, seq_name), \
							seq_statement), "\n\n")
			self.additional_constraints = \
							string.join((self.additional_constraints, \
							"CONSTRAINT %s_PKEY PRIMARY KEY (%s)" % \
							(string.upper(table_name), string.upper(colname))), \
							"\n")
			col_statement = string.join((colname, coltype, \
					  "NOT NULL DEFAULT NEXTVAL('%s')" % seq_name), " ")
		else:
			col_statement = string.join((colname, coltype, colvalue), " ")
		return col_statement	

	def create_references(self, table_id):	
		references = self.tables[table_id].get_refs()
		table_name = self.tables[table_id].name
		for ref in references:
			refname = string.upper(self.tables[table_id].get_ref_name(ref))
			reflocal = string.upper(self.tables[table_id].get_ref_local(ref))
			reftable = string.upper(self.tables[table_id].get_ref_table(ref))
			reftablename = string.upper(self.tables[reftable].name)
			refforeign = \
					string.upper(self.tables[table_id].get_ref_foriegn(ref))
			ref_statement = 'CONSTRAINT %s FOREIGN KEY(%s) REFERENCES %s (%s)'%\
				(str(refname), str(reflocal), str(reftablename), str(refforeign))
			ref_statement = "ALTER TABLE %s ADD %s;" % \
					(string.upper(table_name), ref_statement)
			self.sql_constraint = string.join((self.sql_constraint, \
							fill_header(foriegn_header, refname), \
							ref_statement),"\n\n")

class MySQLBuilder(SQLBuilder):

	def __init__(self, tables):
		SQLBuilder.__init__(self, tables)

	def fix_datatype(self, coltype):
		if coltype == 'timestamp':
			return 'datetime'
		else:	
			return coltype

	def fix_value(self, table_name, colname, coltype, colvalue):		
		if colvalue == 'identity':
			self.additional_constraints = \
							string.join((self.additional_constraints, \
							"PRIMARY KEY (%s)" %  string.upper(colname)), "\n")
			col_statement = string.join((colname, coltype, \
					  "NOT NULL AUTO_INCREMENT"), " ")
		else:
			col_statement = string.join((colname, coltype, colvalue), " ")
		return col_statement	


if __name__=='__main__':
#	filename = sys.argv[1]
	from Options import OptionClass, Configure, GetOptions, myOptions
	optctl = {'f':'filename', 'filename':'filename', \
				'm':'mysql', 'mysql':'mysql', \
				'p':'postgresql', 'postgresql':'postgresql'}
	GetOptions(optctl, 'filename=s', 'f=s', \
			  'm=s', 'mysql=s', 'p=s', 'postgresql=s')

	def bailout(reason):
		print reason
		arglist = optctl.keys()
		arglist.sort()
		numargs = len(arglist)
		print "Usage:"
		for key_index in range(0, numargs, 2):
			print sys.argv[0], \
					'--'+arglist[key_index], \
					' --'+arglist[key_index+1]


	filename = None
	ignore_list = None
	if myOptions.filename:
		filename = myOptions.filename
		if os.path.exists(filename):
			diaxml_obj = DiaContentHandler(filename)
			tables = diaxml_obj.get_data()		
		else:
			bailout("You must specify a filename")
	else:
		bailout("You must specify a filename")

	if myOptions.postgresql:		
		sql_builder_obj = PostgresBuilder(tables)
		sql_builder_obj.make_database()		
		outfile = open(myOptions.postgresql, "w")
		outfile.write(sql_builder_obj.sql_create)
		outfile.flush()
	if myOptions.mysql:
		sql_builder_obj = MySQLBuilder(tables)
		sql_builder_obj.make_database()		
		outfile = open(myOptions.mysql, "w")
		outfile.write(sql_builder_obj.sql_create)
		outfile.flush()
